package com.arrcen.medicen.core.match.helper;

import java.sql.*;
import java.util.List;

public class OracleHelper {

    //定义变量
    private static Connection ct = null;
    //大多数情况下用preparedstatement替代statement
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    //连接数据库的参数
    private static String driver = "";
    private static String url = "";
    private static String username = "";
    private static String passwd = "";

    private static CallableStatement cs = null;

    public static CallableStatement getCs() {
        return cs;
    }


    //加载驱动，只需要一次，用静态代码块
    public static void init(
            String _driver,
            String _url ,
            String _username ,
            String _passwd ) {
        try {
            driver = _driver;
            url = _url;
            username = _username;
            passwd = _passwd;
            close();
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //得到连接
    public static Connection getConnection() {
        try {
            ct = DriverManager.getConnection(url, username, passwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ct;
    }


    //*************callPro1存储过程函数1*************
    public static CallableStatement callPro1(String sql, String[] parameters) {
        try {
            ct = getConnection();
            cs = ct.prepareCall(sql);
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    cs.setObject(i + 1, parameters[i]);
                }
            }
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            close(rs, cs, ct);
        }
        return cs;
    }

    //*******************callpro2存储过程2************************
    public static CallableStatement callPro2(String sql, String[] inparameters,
                                             Integer[] outparameters) {
        try {
            ct = getConnection();
            cs = ct.prepareCall(sql);
            if (inparameters != null) {
                for (int i = 0; i < inparameters.length; i++) {
                    cs.setObject(i + 1, inparameters[i]);
                }
            }
            //cs.registerOutparameter(2,oracle.jdbc.OracleTypes.CURSOR);
            if (outparameters != null) {
                for (int i = 0; i < outparameters.length; i++) {
                    cs.registerOutParameter(inparameters.length + 1 + i, outparameters[i]);
                }
            }
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {

        }
        return cs;
    }

    public static ResultSet executeQuery(String sql, String[] parameters) {
        try {
            if(ct==null|| ct.isClosed()){
                ct = getConnection();
            }

            ps = ct.prepareStatement(sql);
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    ps.setString(i + 1, parameters[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {

        }
        return rs;
    }


    public static Connection getCt() {
        return ct;
    }

    public static PreparedStatement getPs() {
        return ps;
    }

    public static ResultSet getRs() {
        return rs;
    }


    public static void executeUpdate2(List<String> sql, String[][] parameters) {
        try {
            ct = getConnection();
            ct.setAutoCommit(false);

            for (int i = 0; i < sql.size(); i++) {

                if (null != parameters[i]) {
                    ps = ct.prepareStatement(sql.get(i));
                    for (int j = 0; j < parameters[i].length; j++) {
                        ps.setString(j + 1, parameters[i][j]);
                    }
                    ps.executeUpdate();
                }

            }


            ct.commit();


        } catch (Exception e) {
            e.printStackTrace();
            try {
                ct.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw new RuntimeException(e.getMessage());
        } finally {
            close(rs, ps, ct);
        }

    }

    //先写一个update、delete、insert
    //sql格式：update 表名 set 字段名 =？where 字段=？
    //parameter神应该是（”abc“,23）
    public static void executeUpdate(String sql, String[] parameters) {
        try {
            ct = getConnection();
            ps = ct.prepareStatement(sql);
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    ps.setString(i + 1, parameters[i]);
                }

            }
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();//开发阶段
            //抛出异常
            //可以处理，也可以不处理
            throw new RuntimeException(e.getMessage());
        } finally {
            close(rs, ps, ct);
        }
    }

    /** 判断数据库是否支持批处理 */
    public static boolean supportBatch() {
        try {
            ct = getConnection();
            DatabaseMetaData md = ct.getMetaData();
            return md.supportsBatchUpdates();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /** 执行一批SQL语句 */
    public static int[] insertList(List<String> sqls) {
        if (sqls == null) {
            return null;
        }
        Statement sm = null;
        try {
            ct = getConnection();
            sm = ct.createStatement();
            for (int i = 0; i < sqls.size(); i++) {
                sm.addBatch(sqls.get(i));// 将所有的SQL语句添加到Statement中
            }
            // 一次执行多条SQL语句
            return sm.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                sm.close();
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static void close(ResultSet rs, Statement ps, Connection ct) {
        //关闭资源(先开后关)
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ps = null;
        }
        if (null != ct) {
            try {
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ct = null;
        }
    }

    public static void close(ResultSet rs) {

        //关闭资源(先开后关)
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ps = null;
        }
        if (null != ct) {
            try {
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ct = null;
        }
    }

    public static void close() {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ps = null;
        }
        if (null != ct) {
            try {
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ct = null;
        }
    }
}
